graph ER {
fontname="Helvetica,Arial,sans-serif"
fontsize=30;
bgcolor=transparent;
layout=neato
scale=1.2
node [fontname="Helvetica,Arial,sans-serif",fontsize=10]
// Define nodes
node [shape=box];
employee; department; job; job_history; job_grade; location; country; region;
node [shape=ellipse];
employee_id; first_name; last_name; email; phone_number; hire_date; job_id; salary; commission_pct; manager_id; department_id;
department_id; department_name; location_id; manager_id;
job_id; job_title; min_salary; max_salary;
grade_level; lowest_sal; highest_sal;
location_id; street_address; postal_code; city; state_province; country_id;
country_id; country_name; region_id;
region_id; region_name;
node [shape=diamond,style=filled,color=lightgrey];
"Works_In"; "Located_In"; "Part_Of"; "Has_Job"; "History"; "Graded";
// Define edges
employee -- employee_id;
employee -- first_name;
employee -- last_name;
employee -- email;
employee -- phone_number;
employee -- hire_date;
employee -- job_id;
employee -- salary;
employee -- commission_pct;
employee -- manager_id;
employee -- department_id;
employee -- "Works_In" [label="m"];
"Works_In" -- department [label="1"];
department -- department_id;
department -- department_name;
department -- location_id;
department -- manager_id;
department -- "Located_In" [label="1"];
"Located_In" -- location [label="1"];
location -- location_id;
location -- street_address;
location -- postal_code;
location -- city;
location -- state_province;
location -- country_id;
location -- "Part_Of" [label="1"];
"Part_Of" -- country [label="1"];
country -- country_id;
country -- country_name;
country -- region_id;
country -- "Part_Of" [label="1"];
"Part_Of" -- region [label="1"];
region -- region_id;
region -- region_name;
employee -- "Has_Job" [label="1"];
"Has_Job" -- job [label="1"];
job -- job_id;
job -- job_title;
job -- min_salary;
job -- max_salary;
job -- "Graded" [label="1"];
"Graded" -- job_grade [label="1"];
job_grade -- grade_level;
job_grade -- lowest_sal;
job_grade -- highest_sal;
employee -- "History" [label="m"];
"History" -- job_history [label="1"];
job_history -- job_id;
job_history -- department_id;
job_history -- employee_id;
job_history -- start_date;
job_history -- end_date;
}Homework 5 - Documenting an HR database
Thank you for hiring me to take on this role of working directly on you database! After having received credentials to the HR database, I am ready to start revising it to better fit our needs! In this report, I am going to create:
A general written description of contents of database.
A Chen diagram highlighting the major entities and relations.
A Crows-foot diagram highlighting the entities, attributes, and the cardinality and participation of the relations.
A python-generated listing of SQL tables and fields, and a listing of sample data showing the first bunch (5 or 10 or so) of EACH table.
A HTML report, which will be uploaded to canvas.
Github repo: https://github.com/cmsc-vcu/cmsc408-sp2025-hw5-serrotrehpotsirhc.git
Database design
The following sections document the general design of the HR database.
General description
This database represents the Human Resources of the fortune company I have been hired by! There are multiple sections of data for each employee managed by HR. These sections include:
- Countries
- Departments
- Employees
- Emp_details_view
- Jobs
- Job_grades
- Job_history
- Locations
- Regions.
The purpose of this database is to manage each employee and their relevant data. The major tables are listed above.
Hierarchical Relationships
Regions contain countries.
Countries contain locations.
Locations house departments.
Departments employ workers.
Employees have managers, creating a reporting structure.
The purpose of this database is to manage its employees and store their relevant data.
Organizational Structure Tables
Departments Table
Stores department data:
Department ID
Department name
Manager ID
Location ID
Locations Table
Stores the location of company departments:
Location ID
Street address
Postal code
City
State/Province
Country ID
Countries Table
Stores data of countries where the company operates:
Country ID
Country name
Region ID
Regions Table
Stores data of regions:
Region ID
Region name
Chen diagram
The following diagram highlights the major relations in the database.
Crows foot diagram
The following diagram provides greater detail on the entities and attributes and highlights the cardinality and participation of the relations.
erDiagram
EMPLOYEE {
int employeeID
string first_name
string last_name
string email
string phone_number
date hire_date
int salary
float commission_pct
}
DEPARTMENT {
int departmentID
string department_name
}
JOB {
int jobID
string job_title
int min_salary
int max_salary
}
JOB_GRADE {
string job_grade_level
int lowest_sal
int highest_sal
}
JOB_HISTORY {
int jobID
int departmentID
date start_date
date end_date
}
LOCATION {
int locationID
string street_address
string postal_code
string city
string state_province
}
COUNTRY {
string countryID
string country_name
}
REGION {
int regionID
string region_name
}
EMPLOYEE ||--o{ DEPARTMENT : works_in
DEPARTMENT ||--o{ LOCATION : located_in
LOCATION ||--o{ COUNTRY : part_of
COUNTRY ||--o{ REGION : part_of
EMPLOYEE ||--|| JOB : has
JOB ||--o{ JOB_GRADE : graded
EMPLOYEE ||--o{ JOB_HISTORY : historyerDiagram
EMPLOYEE {
int employeeID
string first_name
string last_name
string email
string phone_number
date hire_date
int salary
float commission_pct
}
DEPARTMENT {
int departmentID
string department_name
}
JOB {
int jobID
string job_title
int min_salary
int max_salary
}
JOB_GRADE {
string job_grade_level
int lowest_sal
int highest_sal
}
JOB_HISTORY {
int jobID
int departmentID
date start_date
date end_date
}
LOCATION {
int locationID
string street_address
string postal_code
string city
string state_province
}
COUNTRY {
string countryID
string country_name
}
REGION {
int regionID
string region_name
}
EMPLOYEE ||--o{ DEPARTMENT : works_in
DEPARTMENT ||--o{ LOCATION : located_in
LOCATION ||--o{ COUNTRY : part_of
COUNTRY ||--o{ REGION : part_of
EMPLOYEE ||--|| JOB : has
JOB ||--o{ JOB_GRADE : graded
EMPLOYEE ||--o{ JOB_HISTORY : history
Listing of tables in the database
The following python blocks highlight the steps used to document the tables in the database. The code is left in the report to show how fun it was to write python!
Create connection
Creating a connection to the “HR” database. This section of code:
- Interprets the .env file to locate each key
- Imports the necessary helper packages
- Saves each of the keys to the config_map
- Uses the config_map as a key to set up the server using its 4 variables
Feel free to explore the python in the helpers.py file.
from helpers import create_database_engine, run_sql_and_return_df, run_sql_and_return_html, create_db_wrapper
# load credentials from .env file
# load_dotenv()
# Load these variables from first .env file found.
config_map = {
'user': "CMSC408_HW5_USER",
'password': "CMSC408_HW5_PASSWORD",
'host': "CMSC408_HW5_HOST",
'database': "CMSC408_HW5_DB_NAME"
}
cnx, config = create_db_wrapper( config_map )List of tables found in HR database
We took this piece of code from explore.qmd, which selects the table name and type using SQL from the information schema tables of our HR database. Printing each table!
run_sql_and_return_df(cnx,"""
SELECT
TABLE_NAME, TABLE_TYPE
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = 'sp25_torresc6_hr'
ORDER BY
TABLE_NAME
""")| TABLE_NAME | TABLE_TYPE | |
|---|---|---|
| 0 | countries | BASE TABLE |
| 1 | departments | BASE TABLE |
| 2 | emp_details_view | VIEW |
| 3 | employees | BASE TABLE |
| 4 | job_grades | BASE TABLE |
| 5 | job_history | BASE TABLE |
| 6 | jobs | BASE TABLE |
| 7 | locations | BASE TABLE |
| 8 | regions | BASE TABLE |
Listing of Tables and sample data
The following sections provide a high-level view of the data found in the HR database.
Here, we not only print the tables, we also print their data! Similar to the code block above, we select table name, but this time we also select position, column name, and column type! This will help us display not only each table, but alos each column or subcategory as well!
For example, the Countries table has the ids of: country_id, country_name, and region_id, each with separate data under their respective columns! This code prints these column names!
run_sql_and_return_html(cnx,f"""
SELECT
TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME, COLUMN_TYPE
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
1=1
AND TABLE_SCHEMA = '{config['database']}'
ORDER BY
TABLE_NAME, ORDINAL_POSITION
""")| TABLE_NAME | ORDINAL_POSITION | COLUMN_NAME | COLUMN_TYPE |
|---|---|---|---|
| countries | 1 | country_id | char(2) |
| countries | 2 | country_name | varchar(40) |
| countries | 3 | region_id | int |
| departments | 1 | department_id | int |
| departments | 2 | department_name | varchar(30) |
| departments | 3 | manager_id | int |
| departments | 4 | location_id | int |
| emp_details_view | 1 | employee_id | int |
| emp_details_view | 2 | job_id | varchar(10) |
| emp_details_view | 3 | manager_id | int |
| emp_details_view | 4 | department_id | int |
| emp_details_view | 5 | location_id | int |
| emp_details_view | 6 | country_id | char(2) |
| emp_details_view | 7 | first_name | varchar(20) |
| emp_details_view | 8 | last_name | varchar(25) |
| emp_details_view | 9 | salary | decimal(8,2) |
| emp_details_view | 10 | commission_pct | decimal(2,2) |
| emp_details_view | 11 | department_name | varchar(30) |
| emp_details_view | 12 | job_title | varchar(35) |
| emp_details_view | 13 | city | varchar(30) |
| emp_details_view | 14 | state_province | varchar(25) |
| emp_details_view | 15 | country_name | varchar(40) |
| emp_details_view | 16 | region_name | varchar(25) |
| employees | 1 | employee_id | int |
| employees | 2 | first_name | varchar(20) |
| employees | 3 | last_name | varchar(25) |
| employees | 4 | varchar(25) | |
| employees | 5 | phone_number | varchar(20) |
| employees | 6 | hire_date | date |
| employees | 7 | job_id | varchar(10) |
| employees | 8 | salary | decimal(8,2) |
| employees | 9 | commission_pct | decimal(2,2) |
| employees | 10 | manager_id | int |
| employees | 11 | department_id | int |
| job_grades | 1 | grade_level | varchar(3) |
| job_grades | 2 | lowest_sal | float |
| job_grades | 3 | highest_sal | float |
| job_history | 1 | employee_id | int |
| job_history | 2 | start_date | date |
| job_history | 3 | end_date | date |
| job_history | 4 | job_id | varchar(10) |
| job_history | 5 | department_id | int |
| jobs | 1 | job_id | varchar(10) |
| jobs | 2 | job_title | varchar(35) |
| jobs | 3 | min_salary | float |
| jobs | 4 | max_salary | float |
| locations | 1 | location_id | int |
| locations | 2 | street_address | varchar(40) |
| locations | 3 | postal_code | varchar(12) |
| locations | 4 | city | varchar(30) |
| locations | 5 | state_province | varchar(25) |
| locations | 6 | country_id | char(2) |
| regions | 1 | region_id | int |
| regions | 2 | region_name | varchar(25) |
| Total Rows: 54, Total Columns: 4 | |||
run_sql_and_return_df(cnx,f"""
SELECT
TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME, COLUMN_TYPE
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
1=1
AND TABLE_SCHEMA = '{config['database']}'
ORDER BY
TABLE_NAME, ORDINAL_POSITION
""")| TABLE_NAME | ORDINAL_POSITION | COLUMN_NAME | COLUMN_TYPE | |
|---|---|---|---|---|
| 0 | countries | 1 | country_id | char(2) |
| 1 | countries | 2 | country_name | varchar(40) |
| 2 | countries | 3 | region_id | int |
| 3 | departments | 1 | department_id | int |
| 4 | departments | 2 | department_name | varchar(30) |
| 5 | departments | 3 | manager_id | int |
| 6 | departments | 4 | location_id | int |
| 7 | emp_details_view | 1 | employee_id | int |
| 8 | emp_details_view | 2 | job_id | varchar(10) |
| 9 | emp_details_view | 3 | manager_id | int |
| 10 | emp_details_view | 4 | department_id | int |
| 11 | emp_details_view | 5 | location_id | int |
| 12 | emp_details_view | 6 | country_id | char(2) |
| 13 | emp_details_view | 7 | first_name | varchar(20) |
| 14 | emp_details_view | 8 | last_name | varchar(25) |
| 15 | emp_details_view | 9 | salary | decimal(8,2) |
| 16 | emp_details_view | 10 | commission_pct | decimal(2,2) |
| 17 | emp_details_view | 11 | department_name | varchar(30) |
| 18 | emp_details_view | 12 | job_title | varchar(35) |
| 19 | emp_details_view | 13 | city | varchar(30) |
| 20 | emp_details_view | 14 | state_province | varchar(25) |
| 21 | emp_details_view | 15 | country_name | varchar(40) |
| 22 | emp_details_view | 16 | region_name | varchar(25) |
| 23 | employees | 1 | employee_id | int |
| 24 | employees | 2 | first_name | varchar(20) |
| 25 | employees | 3 | last_name | varchar(25) |
| 26 | employees | 4 | varchar(25) | |
| 27 | employees | 5 | phone_number | varchar(20) |
| 28 | employees | 6 | hire_date | date |
| 29 | employees | 7 | job_id | varchar(10) |
| 30 | employees | 8 | salary | decimal(8,2) |
| 31 | employees | 9 | commission_pct | decimal(2,2) |
| 32 | employees | 10 | manager_id | int |
| 33 | employees | 11 | department_id | int |
| 34 | job_grades | 1 | grade_level | varchar(3) |
| 35 | job_grades | 2 | lowest_sal | float |
| 36 | job_grades | 3 | highest_sal | float |
| 37 | job_history | 1 | employee_id | int |
| 38 | job_history | 2 | start_date | date |
| 39 | job_history | 3 | end_date | date |
| 40 | job_history | 4 | job_id | varchar(10) |
| 41 | job_history | 5 | department_id | int |
| 42 | jobs | 1 | job_id | varchar(10) |
| 43 | jobs | 2 | job_title | varchar(35) |
| 44 | jobs | 3 | min_salary | float |
| 45 | jobs | 4 | max_salary | float |
| 46 | locations | 1 | location_id | int |
| 47 | locations | 2 | street_address | varchar(40) |
| 48 | locations | 3 | postal_code | varchar(12) |
| 49 | locations | 4 | city | varchar(30) |
| 50 | locations | 5 | state_province | varchar(25) |
| 51 | locations | 6 | country_id | char(2) |
| 52 | regions | 1 | region_id | int |
| 53 | regions | 2 | region_name | varchar(25) |
Table: countries
The counties table contains data about the locations of the various offices and departments of the company.
Schema: countries
run_sql_and_return_html(cnx,f"""
SELECT
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE,
COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'countries' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")| COLUMN_NAME | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | IS_NULLABLE | COLUMN_KEY |
|---|---|---|---|---|
| country_id | char | 2.0 | NO | PRI |
| country_name | varchar | 40.0 | YES | |
| region_id | int | NULL | YES | MUL |
| Total Rows: 3, Total Columns: 5 | ||||
Sample data: countries
run_sql_and_return_html(cnx,f"""
SELECT
country_id,
country_name,
region_id
FROM
countries;
""")| country_id | country_name | region_id |
|---|---|---|
| AR | Argentina | 2 |
| AU | Australia | 3 |
| BE | Belgium | 1 |
| BR | Brazil | 2 |
| CA | Canada | 2 |
| CH | Switzerland | 1 |
| CN | China | 3 |
| DE | Germany | 1 |
| DK | Denmark | 1 |
| EG | Egypt | 4 |
| FR | France | 1 |
| IL | Israel | 4 |
| IN | India | 3 |
| IT | Italy | 1 |
| JP | Japan | 3 |
| KW | Kuwait | 4 |
| ML | Malaysia | 3 |
| MX | Mexico | 2 |
| NG | Nigeria | 4 |
| NL | Netherlands | 1 |
| SG | Singapore | 3 |
| UK | United Kingdom | 1 |
| US | United States of America | 2 |
| ZM | Zambia | 4 |
| ZW | Zimbabwe | 4 |
| Total Rows: 25, Total Columns: 3 | ||
Table: departments
The departments table contains …
Schema: departments
run_sql_and_return_html(cnx,f"""
SELECT
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE,
COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'departments' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")| COLUMN_NAME | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | IS_NULLABLE | COLUMN_KEY |
|---|---|---|---|---|
| department_id | int | NULL | NO | PRI |
| department_name | varchar | 30.0 | NO | MUL |
| manager_id | int | NULL | YES | MUL |
| location_id | int | NULL | YES | MUL |
| Total Rows: 4, Total Columns: 5 | ||||
Sample data: departments
run_sql_and_return_html(cnx,f"""
SELECT
department_id, department_name, manager_id, location_id
FROM
departments;
""")| department_id | department_name | manager_id | location_id |
|---|---|---|---|
| 10 | Administration | 200.0 | 1700 |
| 20 | Marketing | 201.0 | 1800 |
| 30 | Purchasing | 114.0 | 1700 |
| 40 | Human Resources | 203.0 | 2400 |
| 50 | Shipping | 121.0 | 1500 |
| 60 | IT | 103.0 | 1400 |
| 70 | Public Relations | 204.0 | 2700 |
| 80 | Sales | 145.0 | 2500 |
| 90 | Executive | 100.0 | 1700 |
| 100 | Finance | 108.0 | 1700 |
| 110 | Accounting | 205.0 | 1700 |
| 120 | Treasury | NULL | 1700 |
| 130 | Corporate Tax | NULL | 1700 |
| 140 | Control And Credit | NULL | 1700 |
| 150 | Shareholder Services | NULL | 1700 |
| 160 | Benefits | NULL | 1700 |
| 170 | Manufacturing | NULL | 1700 |
| 180 | Construction | NULL | 1700 |
| 190 | Contracting | NULL | 1700 |
| 200 | Operations | NULL | 1700 |
| 210 | IT Support | NULL | 1700 |
| 220 | NOC | NULL | 1700 |
| 230 | IT Helpdesk | NULL | 1700 |
| 240 | Government Sales | NULL | 1700 |
| 250 | Retail Sales | NULL | 1700 |
| 260 | Recruiting | NULL | 1700 |
| 270 | Payroll | NULL | 1700 |
| Total Rows: 27, Total Columns: 4 | |||
Table: employees
The employees table contains …
Schema: employees
run_sql_and_return_html(cnx,f"""
SELECT
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE,
COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'employees' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")| COLUMN_NAME | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | IS_NULLABLE | COLUMN_KEY |
|---|---|---|---|---|
| employee_id | int | NULL | NO | PRI |
| first_name | varchar | 20.0 | YES | |
| last_name | varchar | 25.0 | NO | MUL |
| varchar | 25.0 | NO | UNI | |
| phone_number | varchar | 20.0 | YES | |
| hire_date | date | NULL | NO | |
| job_id | varchar | 10.0 | NO | MUL |
| salary | decimal | NULL | YES | |
| commission_pct | decimal | NULL | YES | |
| manager_id | int | NULL | YES | MUL |
| department_id | int | NULL | YES | MUL |
| Total Rows: 11, Total Columns: 5 | ||||
Sample data: employees
run_sql_and_return_html(cnx,f"""
SELECT
employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id
FROM employees;
""")| employee_id | first_name | last_name | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id | |
|---|---|---|---|---|---|---|---|---|---|---|
| 100 | Steven | King | SKING | 515.123.4567 | 2003-06-17 | AD_PRES | 24000.0 | NULL | NULL | 90.0 |
| 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 2005-09-21 | AD_VP | 17000.0 | NULL | 100.0 | 90.0 |
| 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 2001-01-13 | AD_VP | 17000.0 | NULL | 100.0 | 90.0 |
| 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 2006-01-03 | IT_PROG | 9000.0 | NULL | 102.0 | 60.0 |
| 104 | Bruce | Ernst | BERNST | 590.423.4568 | 2007-05-21 | IT_PROG | 6000.0 | NULL | 103.0 | 60.0 |
| 105 | David | Austin | DAUSTIN | 590.423.4569 | 2005-06-25 | IT_PROG | 4800.0 | NULL | 103.0 | 60.0 |
| 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 2006-02-05 | IT_PROG | 4800.0 | NULL | 103.0 | 60.0 |
| 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 2007-02-07 | IT_PROG | 4200.0 | NULL | 103.0 | 60.0 |
| 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 2002-08-17 | FI_MGR | 12008.0 | NULL | 101.0 | 100.0 |
| 109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 2002-08-16 | FI_ACCOUNT | 9000.0 | NULL | 108.0 | 100.0 |
| 110 | John | Chen | JCHEN | 515.124.4269 | 2005-09-28 | FI_ACCOUNT | 8200.0 | NULL | 108.0 | 100.0 |
| 111 | Ismael | Sciarra | ISCIARRA | 515.124.4369 | 2005-09-30 | FI_ACCOUNT | 7700.0 | NULL | 108.0 | 100.0 |
| 112 | Jose Manuel | Urman | JMURMAN | 515.124.4469 | 2006-03-07 | FI_ACCOUNT | 7800.0 | NULL | 108.0 | 100.0 |
| 113 | Luis | Popp | LPOPP | 515.124.4567 | 2007-12-07 | FI_ACCOUNT | 6900.0 | NULL | 108.0 | 100.0 |
| 114 | Den | Raphaely | DRAPHEAL | 515.127.4561 | 2002-12-07 | PU_MAN | 11000.0 | NULL | 100.0 | 30.0 |
| 115 | Alexander | Khoo | AKHOO | 515.127.4562 | 2003-05-18 | PU_CLERK | 3100.0 | NULL | 114.0 | 30.0 |
| 116 | Shelli | Baida | SBAIDA | 515.127.4563 | 2005-12-24 | PU_CLERK | 2900.0 | NULL | 114.0 | 30.0 |
| 117 | Sigal | Tobias | STOBIAS | 515.127.4564 | 2005-07-24 | PU_CLERK | 2800.0 | NULL | 114.0 | 30.0 |
| 118 | Guy | Himuro | GHIMURO | 515.127.4565 | 2006-11-15 | PU_CLERK | 2600.0 | NULL | 114.0 | 30.0 |
| 119 | Karen | Colmenares | KCOLMENA | 515.127.4566 | 2007-08-10 | PU_CLERK | 2500.0 | NULL | 114.0 | 30.0 |
| 120 | Matthew | Weiss | MWEISS | 650.123.1234 | 2004-07-18 | ST_MAN | 8000.0 | NULL | 100.0 | 50.0 |
| 121 | Adam | Fripp | AFRIPP | 650.123.2234 | 2005-04-10 | ST_MAN | 8200.0 | NULL | 100.0 | 50.0 |
| 122 | Payam | Kaufling | PKAUFLIN | 650.123.3234 | 2003-05-01 | ST_MAN | 7900.0 | NULL | 100.0 | 50.0 |
| 123 | Shanta | Vollman | SVOLLMAN | 650.123.4234 | 2005-10-10 | ST_MAN | 6500.0 | NULL | 100.0 | 50.0 |
| 124 | Kevin | Mourgos | KMOURGOS | 650.123.5234 | 2007-11-16 | ST_MAN | 5800.0 | NULL | 100.0 | 50.0 |
| 125 | Julia | Nayer | JNAYER | 650.124.1214 | 2005-07-16 | ST_CLERK | 3200.0 | NULL | 120.0 | 50.0 |
| 126 | Irene | Mikkilineni | IMIKKILI | 650.124.1224 | 2006-09-28 | ST_CLERK | 2700.0 | NULL | 120.0 | 50.0 |
| 127 | James | Landry | JLANDRY | 650.124.1334 | 2007-01-14 | ST_CLERK | 2400.0 | NULL | 120.0 | 50.0 |
| 128 | Steven | Markle | SMARKLE | 650.124.1434 | 2008-03-08 | ST_CLERK | 2200.0 | NULL | 120.0 | 50.0 |
| 129 | Laura | Bissot | LBISSOT | 650.124.5234 | 2005-08-20 | ST_CLERK | 3300.0 | NULL | 121.0 | 50.0 |
| 130 | Mozhe | Atkinson | MATKINSO | 650.124.6234 | 2005-10-30 | ST_CLERK | 2800.0 | NULL | 121.0 | 50.0 |
| 131 | James | Marlow | JAMRLOW | 650.124.7234 | 2005-02-16 | ST_CLERK | 2500.0 | NULL | 121.0 | 50.0 |
| 132 | TJ | Olson | TJOLSON | 650.124.8234 | 2007-04-10 | ST_CLERK | 2100.0 | NULL | 121.0 | 50.0 |
| 133 | Jason | Mallin | JMALLIN | 650.127.1934 | 2004-06-14 | ST_CLERK | 3300.0 | NULL | 122.0 | 50.0 |
| 134 | Michael | Rogers | MROGERS | 650.127.1834 | 2006-08-26 | ST_CLERK | 2900.0 | NULL | 122.0 | 50.0 |
| 135 | Ki | Gee | KGEE | 650.127.1734 | 2007-12-12 | ST_CLERK | 2400.0 | NULL | 122.0 | 50.0 |
| 136 | Hazel | Philtanker | HPHILTAN | 650.127.1634 | 2008-02-06 | ST_CLERK | 2200.0 | NULL | 122.0 | 50.0 |
| 137 | Renske | Ladwig | RLADWIG | 650.121.1234 | 2003-07-14 | ST_CLERK | 3600.0 | NULL | 123.0 | 50.0 |
| 138 | Stephen | Stiles | SSTILES | 650.121.2034 | 2005-10-26 | ST_CLERK | 3200.0 | NULL | 123.0 | 50.0 |
| 139 | John | Seo | JSEO | 650.121.2019 | 2006-02-12 | ST_CLERK | 2700.0 | NULL | 123.0 | 50.0 |
| 140 | Joshua | Patel | JPATEL | 650.121.1834 | 2006-04-06 | ST_CLERK | 2500.0 | NULL | 123.0 | 50.0 |
| 141 | Trenna | Rajs | TRAJS | 650.121.8009 | 2003-10-17 | ST_CLERK | 3500.0 | NULL | 124.0 | 50.0 |
| 142 | Curtis | Davies | CDAVIES | 650.121.2994 | 2005-01-29 | ST_CLERK | 3100.0 | NULL | 124.0 | 50.0 |
| 143 | Randall | Matos | RMATOS | 650.121.2874 | 2006-03-15 | ST_CLERK | 2600.0 | NULL | 124.0 | 50.0 |
| 144 | Peter | Vargas | PVARGAS | 650.121.2004 | 2006-07-09 | ST_CLERK | 2500.0 | NULL | 124.0 | 50.0 |
| 145 | John | Russell | JRUSSEL | 011.44.1344.429268 | 2004-10-01 | SA_MAN | 14000.0 | 0.40 | 100.0 | 80.0 |
| 146 | Karen | Partners | KPARTNER | 011.44.1344.467268 | 2005-01-05 | SA_MAN | 13500.0 | 0.30 | 100.0 | 80.0 |
| 147 | Alberto | Errazuriz | AERRAZUR | 011.44.1344.429278 | 2005-03-10 | SA_MAN | 12000.0 | 0.30 | 100.0 | 80.0 |
| 148 | Gerald | Cambrault | GCAMBRAU | 011.44.1344.619268 | 2007-10-15 | SA_MAN | 11000.0 | 0.30 | 100.0 | 80.0 |
| 149 | Eleni | Zlotkey | EZLOTKEY | 011.44.1344.429018 | 2008-01-29 | SA_MAN | 10500.0 | 0.20 | 100.0 | 80.0 |
| 150 | Peter | Tucker | PTUCKER | 011.44.1344.129268 | 2005-01-30 | SA_REP | 10000.0 | 0.30 | 145.0 | 80.0 |
| 151 | David | Bernstein | DBERNSTE | 011.44.1344.345268 | 2005-03-24 | SA_REP | 9500.0 | 0.25 | 145.0 | 80.0 |
| 152 | Peter | Hall | PHALL | 011.44.1344.478968 | 2005-08-20 | SA_REP | 9000.0 | 0.25 | 145.0 | 80.0 |
| 153 | Christopher | Olsen | COLSEN | 011.44.1344.498718 | 2006-03-30 | SA_REP | 8000.0 | 0.20 | 145.0 | 80.0 |
| 154 | Nanette | Cambrault | NCAMBRAU | 011.44.1344.987668 | 2006-12-09 | SA_REP | 7500.0 | 0.20 | 145.0 | 80.0 |
| 155 | Oliver | Tuvault | OTUVAULT | 011.44.1344.486508 | 2007-11-23 | SA_REP | 7000.0 | 0.15 | 145.0 | 80.0 |
| 156 | Janette | King | JKING | 011.44.1345.429268 | 2004-01-30 | SA_REP | 10000.0 | 0.35 | 146.0 | 80.0 |
| 157 | Patrick | Sully | PSULLY | 011.44.1345.929268 | 2004-03-04 | SA_REP | 9500.0 | 0.35 | 146.0 | 80.0 |
| 158 | Allan | McEwen | AMCEWEN | 011.44.1345.829268 | 2004-08-01 | SA_REP | 9000.0 | 0.35 | 146.0 | 80.0 |
| 159 | Lindsey | Smith | LSMITH | 011.44.1345.729268 | 2005-03-10 | SA_REP | 8000.0 | 0.30 | 146.0 | 80.0 |
| 160 | Louise | Doran | LDORAN | 011.44.1345.629268 | 2005-12-15 | SA_REP | 7500.0 | 0.30 | 146.0 | 80.0 |
| 161 | Sarath | Sewall | SSEWALL | 011.44.1345.529268 | 2006-11-03 | SA_REP | 7000.0 | 0.25 | 146.0 | 80.0 |
| 162 | Clara | Vishney | CVISHNEY | 011.44.1346.129268 | 2005-11-11 | SA_REP | 10500.0 | 0.25 | 147.0 | 80.0 |
| 163 | Danielle | Greene | DGREENE | 011.44.1346.229268 | 2007-03-19 | SA_REP | 9500.0 | 0.15 | 147.0 | 80.0 |
| 164 | Mattea | Marvins | MMARVINS | 011.44.1346.329268 | 2008-01-24 | SA_REP | 7200.0 | 0.10 | 147.0 | 80.0 |
| 165 | David | Lee | DLEE | 011.44.1346.529268 | 2008-02-23 | SA_REP | 6800.0 | 0.10 | 147.0 | 80.0 |
| 166 | Sundar | Ande | SANDE | 011.44.1346.629268 | 2008-03-24 | SA_REP | 6400.0 | 0.10 | 147.0 | 80.0 |
| 167 | Amit | Banda | ABANDA | 011.44.1346.729268 | 2008-04-21 | SA_REP | 6200.0 | 0.10 | 147.0 | 80.0 |
| 168 | Lisa | Ozer | LOZER | 011.44.1343.929268 | 2005-03-11 | SA_REP | 11500.0 | 0.25 | 148.0 | 80.0 |
| 169 | Harrison | Bloom | HBLOOM | 011.44.1343.829268 | 2006-03-23 | SA_REP | 10000.0 | 0.20 | 148.0 | 80.0 |
| 170 | Tayler | Fox | TFOX | 011.44.1343.729268 | 2006-01-24 | SA_REP | 9600.0 | 0.20 | 148.0 | 80.0 |
| 171 | William | Smith | WSMITH | 011.44.1343.629268 | 2007-02-23 | SA_REP | 7400.0 | 0.15 | 148.0 | 80.0 |
| 172 | Elizabeth | Bates | EBATES | 011.44.1343.529268 | 2007-03-24 | SA_REP | 7300.0 | 0.15 | 148.0 | 80.0 |
| 173 | Sundita | Kumar | SKUMAR | 011.44.1343.329268 | 2008-04-21 | SA_REP | 6100.0 | 0.10 | 148.0 | 80.0 |
| 174 | Ellen | Abel | EABEL | 011.44.1644.429267 | 2004-05-11 | SA_REP | 11000.0 | 0.30 | 149.0 | 80.0 |
| 175 | Alyssa | Hutton | AHUTTON | 011.44.1644.429266 | 2005-03-19 | SA_REP | 8800.0 | 0.25 | 149.0 | 80.0 |
| 176 | Jonathon | Taylor | JTAYLOR | 011.44.1644.429265 | 2006-03-24 | SA_REP | 8600.0 | 0.20 | 149.0 | 80.0 |
| 177 | Jack | Livingston | JLIVINGS | 011.44.1644.429264 | 2006-04-23 | SA_REP | 8400.0 | 0.20 | 149.0 | 80.0 |
| 178 | Kimberely | Grant | KGRANT | 011.44.1644.429263 | 2007-05-24 | SA_REP | 7000.0 | 0.15 | 149.0 | NULL |
| 179 | Charles | Johnson | CJOHNSON | 011.44.1644.429262 | 2008-01-04 | SA_REP | 6200.0 | 0.10 | 149.0 | 80.0 |
| 180 | Winston | Taylor | WTAYLOR | 650.507.9876 | 2006-01-24 | SH_CLERK | 3200.0 | NULL | 120.0 | 50.0 |
| 181 | Jean | Fleaur | JFLEAUR | 650.507.9877 | 2006-02-23 | SH_CLERK | 3100.0 | NULL | 120.0 | 50.0 |
| 182 | Martha | Sullivan | MSULLIVA | 650.507.9878 | 2007-06-21 | SH_CLERK | 2500.0 | NULL | 120.0 | 50.0 |
| 183 | Girard | Geoni | GGEONI | 650.507.9879 | 2008-02-03 | SH_CLERK | 2800.0 | NULL | 120.0 | 50.0 |
| 184 | Nandita | Sarchand | NSARCHAN | 650.509.1876 | 2004-01-27 | SH_CLERK | 4200.0 | NULL | 121.0 | 50.0 |
| 185 | Alexis | Bull | ABULL | 650.509.2876 | 2005-02-20 | SH_CLERK | 4100.0 | NULL | 121.0 | 50.0 |
| 186 | Julia | Dellinger | JDELLING | 650.509.3876 | 2006-06-24 | SH_CLERK | 3400.0 | NULL | 121.0 | 50.0 |
| 187 | Anthony | Cabrio | ACABRIO | 650.509.4876 | 2007-02-07 | SH_CLERK | 3000.0 | NULL | 121.0 | 50.0 |
| 188 | Kelly | Chung | KCHUNG | 650.505.1876 | 2005-06-14 | SH_CLERK | 3800.0 | NULL | 122.0 | 50.0 |
| 189 | Jennifer | Dilly | JDILLY | 650.505.2876 | 2005-08-13 | SH_CLERK | 3600.0 | NULL | 122.0 | 50.0 |
| 190 | Timothy | Gates | TGATES | 650.505.3876 | 2006-07-11 | SH_CLERK | 2900.0 | NULL | 122.0 | 50.0 |
| 191 | Randall | Perkins | RPERKINS | 650.505.4876 | 2007-12-19 | SH_CLERK | 2500.0 | NULL | 122.0 | 50.0 |
| 192 | Sarah | Bell | SBELL | 650.501.1876 | 2004-02-04 | SH_CLERK | 4000.0 | NULL | 123.0 | 50.0 |
| 193 | Britney | Everett | BEVERETT | 650.501.2876 | 2005-03-03 | SH_CLERK | 3900.0 | NULL | 123.0 | 50.0 |
| 194 | Samuel | McCain | SMCCAIN | 650.501.3876 | 2006-07-01 | SH_CLERK | 3200.0 | NULL | 123.0 | 50.0 |
| 195 | Vance | Jones | VJONES | 650.501.4876 | 2007-03-17 | SH_CLERK | 2800.0 | NULL | 123.0 | 50.0 |
| 196 | Alana | Walsh | AWALSH | 650.507.9811 | 2006-04-24 | SH_CLERK | 3100.0 | NULL | 124.0 | 50.0 |
| 197 | Kevin | Feeney | KFEENEY | 650.507.9822 | 2006-05-23 | SH_CLERK | 3000.0 | NULL | 124.0 | 50.0 |
| 198 | Donald | OConnell | DOCONNEL | 650.507.9833 | 2007-06-21 | SH_CLERK | 2600.0 | NULL | 124.0 | 50.0 |
| 199 | Douglas | Grant | DGRANT | 650.507.9844 | 2008-01-13 | SH_CLERK | 2600.0 | NULL | 124.0 | 50.0 |
| 200 | Jennifer | Whalen | JWHALEN | 515.123.4444 | 2003-09-17 | AD_ASST | 4400.0 | NULL | 101.0 | 10.0 |
| 201 | Michael | Hartstein | MHARTSTE | 515.123.5555 | 2004-02-17 | MK_MAN | 13000.0 | NULL | 100.0 | 20.0 |
| 202 | Pat | Fay | PFAY | 603.123.6666 | 2005-08-17 | MK_REP | 6000.0 | NULL | 201.0 | 20.0 |
| 203 | Susan | Mavris | SMAVRIS | 515.123.7777 | 2002-06-07 | HR_REP | 6500.0 | NULL | 101.0 | 40.0 |
| 204 | Hermann | Baer | HBAER | 515.123.8888 | 2002-06-07 | PR_REP | 10000.0 | NULL | 101.0 | 70.0 |
| 205 | Shelley | Higgins | SHIGGINS | 515.123.8080 | 2002-06-07 | AC_MGR | 12008.0 | NULL | 101.0 | 110.0 |
| 206 | William | Gietz | WGIETZ | 515.123.8181 | 2002-06-07 | AC_ACCOUNT | 8300.0 | NULL | 205.0 | 110.0 |
| Total Rows: 107, Total Columns: 11 | ||||||||||
Table: jobs
The jobs table contains …
Schema: jobs
run_sql_and_return_html(cnx,f"""
SELECT
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE,
COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'jobs' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")| COLUMN_NAME | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | IS_NULLABLE | COLUMN_KEY |
|---|---|---|---|---|
| job_id | varchar | 10.0 | NO | PRI |
| job_title | varchar | 35.0 | NO | |
| min_salary | float | NULL | YES | |
| max_salary | float | NULL | YES | |
| Total Rows: 4, Total Columns: 5 | ||||
Sample data: jobs
run_sql_and_return_html(cnx,f"""
SELECT
job_id, job_title, min_salary, max_salary
FROM jobs;
""")| job_id | job_title | min_salary | max_salary |
|---|---|---|---|
| AC_ACCOUNT | Public Accountant | 4200.0 | 9000.0 |
| AC_MGR | Accounting Manager | 8200.0 | 16000.0 |
| AD_ASST | Administration Assistant | 3000.0 | 6000.0 |
| AD_PRES | President | 20080.0 | 40000.0 |
| AD_VP | Administration Vice President | 15000.0 | 30000.0 |
| FI_ACCOUNT | Accountant | 4200.0 | 9000.0 |
| FI_MGR | Finance Manager | 8200.0 | 16000.0 |
| HR_REP | Human Resources Representative | 4000.0 | 9000.0 |
| IT_PROG | Programmer | 4000.0 | 10000.0 |
| MK_MAN | Marketing Manager | 9000.0 | 15000.0 |
| MK_REP | Marketing Representative | 4000.0 | 9000.0 |
| PR_REP | Public Relations Representative | 4500.0 | 10500.0 |
| PU_CLERK | Purchasing Clerk | 2500.0 | 5500.0 |
| PU_MAN | Purchasing Manager | 8000.0 | 15000.0 |
| SA_MAN | Sales Manager | 10000.0 | 20080.0 |
| SA_REP | Sales Representative | 6000.0 | 12008.0 |
| SH_CLERK | Shipping Clerk | 2500.0 | 5500.0 |
| ST_CLERK | Stock Clerk | 2008.0 | 5000.0 |
| ST_MAN | Stock Manager | 5500.0 | 8500.0 |
| Total Rows: 19, Total Columns: 4 | |||
Table: job_grades
The job_grades table contains …
Schema: job_grades
run_sql_and_return_html(cnx,f"""
SELECT
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE,
COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'job_grades' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")| COLUMN_NAME | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | IS_NULLABLE | COLUMN_KEY |
|---|---|---|---|---|
| grade_level | varchar | 3.0 | NO | PRI |
| lowest_sal | float | NULL | YES | |
| highest_sal | float | NULL | YES | |
| Total Rows: 3, Total Columns: 5 | ||||
Sample data: job_grades
run_sql_and_return_html(cnx,f"""
SELECT
grade_level, lowest_sal, highest_sal
FROM job_grades;
""")| grade_level | lowest_sal | highest_sal |
|---|---|---|
| A | 1000.0 | 2999.0 |
| B | 3000.0 | 5999.0 |
| C | 6000.0 | 9999.0 |
| D | 10000.0 | 14999.0 |
| E | 15000.0 | 24999.0 |
| F | 25000.0 | 40000.0 |
| Total Rows: 6, Total Columns: 3 | ||
Table: job_history
The job_history table contains …
Schema: job_history
run_sql_and_return_html(cnx,f"""
SELECT
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE,
COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'job_history' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")| COLUMN_NAME | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | IS_NULLABLE | COLUMN_KEY |
|---|---|---|---|---|
| employee_id | int | NULL | NO | PRI |
| start_date | date | NULL | NO | PRI |
| end_date | date | NULL | NO | PRI |
| job_id | varchar | 10.0 | NO | MUL |
| department_id | int | NULL | YES | MUL |
| Total Rows: 5, Total Columns: 5 | ||||
Sample data: job_history
run_sql_and_return_html(cnx,f"""
SELECT
employee_id, start_date, end_date, job_id, department_id
FROM job_history;
""")| employee_id | start_date | end_date | job_id | department_id |
|---|---|---|---|---|
| 101 | 1997-09-21 | 2001-10-27 | AC_ACCOUNT | 110 |
| 101 | 2001-10-28 | 2005-03-15 | AC_MGR | 110 |
| 102 | 2001-01-13 | 2006-07-24 | IT_PROG | 60 |
| 114 | 2006-03-24 | 2007-12-31 | ST_CLERK | 50 |
| 122 | 2007-01-01 | 2007-12-31 | ST_CLERK | 50 |
| 176 | 2006-03-24 | 2006-12-31 | SA_REP | 80 |
| 176 | 2007-01-01 | 2007-12-31 | SA_MAN | 80 |
| 200 | 1995-09-17 | 2001-06-17 | AD_ASST | 90 |
| 200 | 2002-07-01 | 2006-12-31 | AC_ACCOUNT | 90 |
| 201 | 2004-02-17 | 2007-12-19 | MK_REP | 20 |
| Total Rows: 10, Total Columns: 5 | ||||
Table: locations
The locations table contains …
Schema: locations
run_sql_and_return_html(cnx,f"""
SELECT
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE,
COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'locations' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")| COLUMN_NAME | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | IS_NULLABLE | COLUMN_KEY |
|---|---|---|---|---|
| location_id | int | NULL | NO | PRI |
| street_address | varchar | 40.0 | YES | |
| postal_code | varchar | 12.0 | YES | |
| city | varchar | 30.0 | NO | MUL |
| state_province | varchar | 25.0 | YES | MUL |
| country_id | char | 2.0 | YES | MUL |
| Total Rows: 6, Total Columns: 5 | ||||
Sample data: locations
run_sql_and_return_html(cnx,f"""
SELECT
location_id, street_address, postal_code, city, state_province, country_id
FROM locations;
""")| location_id | street_address | postal_code | city | state_province | country_id |
|---|---|---|---|---|---|
| 1000 | 1297 Via Cola di Rie | 00989 | Roma | NULL | IT |
| 1100 | 93091 Calle della Testa | 10934 | Venice | NULL | IT |
| 1200 | 2017 Shinjuku-ku | 1689 | Tokyo | Tokyo Prefecture | JP |
| 1300 | 9450 Kamiya-cho | 6823 | Hiroshima | NULL | JP |
| 1400 | 2014 Jabberwocky Rd | 26192 | Southlake | Texas | US |
| 1500 | 2011 Interiors Blvd | 99236 | South San Francisco | California | US |
| 1600 | 2007 Zagora St | 50090 | South Brunswick | New Jersey | US |
| 1700 | 2004 Charade Rd | 98199 | Seattle | Washington | US |
| 1800 | 147 Spadina Ave | M5V 2L7 | Toronto | Ontario | CA |
| 1900 | 6092 Boxwood St | YSW 9T2 | Whitehorse | Yukon | CA |
| 2000 | 40-5-12 Laogianggen | 190518 | Beijing | NULL | CN |
| 2100 | 1298 Vileparle (E) | 490231 | Bombay | Maharashtra | IN |
| 2200 | 12-98 Victoria Street | 2901 | Sydney | New South Wales | AU |
| 2300 | 198 Clementi North | 540198 | Singapore | NULL | SG |
| 2400 | 8204 Arthur St | NULL | London | NULL | UK |
| 2500 | Magdalen Centre, The Oxford Science Park | OX9 9ZB | Oxford | Oxford | UK |
| 2600 | 9702 Chester Road | 09629850293 | Stretford | Manchester | UK |
| 2700 | Schwanthalerstr. 7031 | 80925 | Munich | Bavaria | DE |
| 2800 | Rua Frei Caneca 1360 | 01307-002 | Sao Paulo | Sao Paulo | BR |
| 2900 | 20 Rue des Corps-Saints | 1730 | Geneva | Geneve | CH |
| 3000 | Murtenstrasse 921 | 3095 | Bern | BE | CH |
| 3100 | Pieter Breughelstraat 837 | 3029SK | Utrecht | Utrecht | NL |
| 3200 | Mariano Escobedo 9991 | 11932 | Mexico City | Distrito Federal, | MX |
| Total Rows: 23, Total Columns: 6 | |||||
Table: regions
The regions table contains …
Schema: regions
run_sql_and_return_html(cnx,f"""
SELECT
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE,
COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'regions' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")| COLUMN_NAME | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | IS_NULLABLE | COLUMN_KEY |
|---|---|---|---|---|
| region_id | int | NULL | NO | PRI |
| region_name | varchar | 25.0 | YES | |
| Total Rows: 2, Total Columns: 5 | ||||
Sample data: regions
run_sql_and_return_html(cnx,f"""
SELECT
region_id, region_name
FROM regions;
""")| region_id | region_name |
|---|---|
| 1 | Europe |
| 2 | Americas |
| 3 | Asia |
| 4 | Middle East and Africa |
| Total Rows: 4, Total Columns: 2 | |
Table: emp_details_view
The emp_details_view table contains …
Schema: emp_details_view
run_sql_and_return_html(cnx,f"""
SELECT
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE,
COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'emp_details_view' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")| COLUMN_NAME | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | IS_NULLABLE | COLUMN_KEY |
|---|---|---|---|---|
| employee_id | int | NULL | NO | |
| job_id | varchar | 10.0 | NO | |
| manager_id | int | NULL | YES | |
| department_id | int | NULL | YES | |
| location_id | int | NULL | YES | |
| country_id | char | 2.0 | YES | |
| first_name | varchar | 20.0 | YES | |
| last_name | varchar | 25.0 | NO | |
| salary | decimal | NULL | YES | |
| commission_pct | decimal | NULL | YES | |
| department_name | varchar | 30.0 | NO | |
| job_title | varchar | 35.0 | NO | |
| city | varchar | 30.0 | NO | |
| state_province | varchar | 25.0 | YES | |
| country_name | varchar | 40.0 | YES | |
| region_name | varchar | 25.0 | YES | |
| Total Rows: 16, Total Columns: 5 | ||||
Sample data: emp_details_view
run_sql_and_return_html(cnx,f"""
SELECT
employee_id, job_id, manager_id, department_id, location_id, country_id, first_name, last_name, salary, commission_pct, department_name, job_title, city, state_province, country_name, region_name
FROM
emp_details_view
""")| employee_id | job_id | manager_id | department_id | location_id | country_id | first_name | last_name | salary | commission_pct | department_name | job_title | city | state_province | country_name | region_name |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 200 | AD_ASST | 101.0 | 10 | 1700 | US | Jennifer | Whalen | 4400.0 | NULL | Administration | Administration Assistant | Seattle | Washington | United States of America | Americas |
| 201 | MK_MAN | 100.0 | 20 | 1800 | CA | Michael | Hartstein | 13000.0 | NULL | Marketing | Marketing Manager | Toronto | Ontario | Canada | Americas |
| 202 | MK_REP | 201.0 | 20 | 1800 | CA | Pat | Fay | 6000.0 | NULL | Marketing | Marketing Representative | Toronto | Ontario | Canada | Americas |
| 114 | PU_MAN | 100.0 | 30 | 1700 | US | Den | Raphaely | 11000.0 | NULL | Purchasing | Purchasing Manager | Seattle | Washington | United States of America | Americas |
| 115 | PU_CLERK | 114.0 | 30 | 1700 | US | Alexander | Khoo | 3100.0 | NULL | Purchasing | Purchasing Clerk | Seattle | Washington | United States of America | Americas |
| 116 | PU_CLERK | 114.0 | 30 | 1700 | US | Shelli | Baida | 2900.0 | NULL | Purchasing | Purchasing Clerk | Seattle | Washington | United States of America | Americas |
| 117 | PU_CLERK | 114.0 | 30 | 1700 | US | Sigal | Tobias | 2800.0 | NULL | Purchasing | Purchasing Clerk | Seattle | Washington | United States of America | Americas |
| 118 | PU_CLERK | 114.0 | 30 | 1700 | US | Guy | Himuro | 2600.0 | NULL | Purchasing | Purchasing Clerk | Seattle | Washington | United States of America | Americas |
| 119 | PU_CLERK | 114.0 | 30 | 1700 | US | Karen | Colmenares | 2500.0 | NULL | Purchasing | Purchasing Clerk | Seattle | Washington | United States of America | Americas |
| 203 | HR_REP | 101.0 | 40 | 2400 | UK | Susan | Mavris | 6500.0 | NULL | Human Resources | Human Resources Representative | London | NULL | United Kingdom | Europe |
| 120 | ST_MAN | 100.0 | 50 | 1500 | US | Matthew | Weiss | 8000.0 | NULL | Shipping | Stock Manager | South San Francisco | California | United States of America | Americas |
| 121 | ST_MAN | 100.0 | 50 | 1500 | US | Adam | Fripp | 8200.0 | NULL | Shipping | Stock Manager | South San Francisco | California | United States of America | Americas |
| 122 | ST_MAN | 100.0 | 50 | 1500 | US | Payam | Kaufling | 7900.0 | NULL | Shipping | Stock Manager | South San Francisco | California | United States of America | Americas |
| 123 | ST_MAN | 100.0 | 50 | 1500 | US | Shanta | Vollman | 6500.0 | NULL | Shipping | Stock Manager | South San Francisco | California | United States of America | Americas |
| 124 | ST_MAN | 100.0 | 50 | 1500 | US | Kevin | Mourgos | 5800.0 | NULL | Shipping | Stock Manager | South San Francisco | California | United States of America | Americas |
| 125 | ST_CLERK | 120.0 | 50 | 1500 | US | Julia | Nayer | 3200.0 | NULL | Shipping | Stock Clerk | South San Francisco | California | United States of America | Americas |
| 126 | ST_CLERK | 120.0 | 50 | 1500 | US | Irene | Mikkilineni | 2700.0 | NULL | Shipping | Stock Clerk | South San Francisco | California | United States of America | Americas |
| 127 | ST_CLERK | 120.0 | 50 | 1500 | US | James | Landry | 2400.0 | NULL | Shipping | Stock Clerk | South San Francisco | California | United States of America | Americas |
| 128 | ST_CLERK | 120.0 | 50 | 1500 | US | Steven | Markle | 2200.0 | NULL | Shipping | Stock Clerk | South San Francisco | California | United States of America | Americas |
| 129 | ST_CLERK | 121.0 | 50 | 1500 | US | Laura | Bissot | 3300.0 | NULL | Shipping | Stock Clerk | South San Francisco | California | United States of America | Americas |
| 130 | ST_CLERK | 121.0 | 50 | 1500 | US | Mozhe | Atkinson | 2800.0 | NULL | Shipping | Stock Clerk | South San Francisco | California | United States of America | Americas |
| 131 | ST_CLERK | 121.0 | 50 | 1500 | US | James | Marlow | 2500.0 | NULL | Shipping | Stock Clerk | South San Francisco | California | United States of America | Americas |
| 132 | ST_CLERK | 121.0 | 50 | 1500 | US | TJ | Olson | 2100.0 | NULL | Shipping | Stock Clerk | South San Francisco | California | United States of America | Americas |
| 133 | ST_CLERK | 122.0 | 50 | 1500 | US | Jason | Mallin | 3300.0 | NULL | Shipping | Stock Clerk | South San Francisco | California | United States of America | Americas |
| 134 | ST_CLERK | 122.0 | 50 | 1500 | US | Michael | Rogers | 2900.0 | NULL | Shipping | Stock Clerk | South San Francisco | California | United States of America | Americas |
| 135 | ST_CLERK | 122.0 | 50 | 1500 | US | Ki | Gee | 2400.0 | NULL | Shipping | Stock Clerk | South San Francisco | California | United States of America | Americas |
| 136 | ST_CLERK | 122.0 | 50 | 1500 | US | Hazel | Philtanker | 2200.0 | NULL | Shipping | Stock Clerk | South San Francisco | California | United States of America | Americas |
| 137 | ST_CLERK | 123.0 | 50 | 1500 | US | Renske | Ladwig | 3600.0 | NULL | Shipping | Stock Clerk | South San Francisco | California | United States of America | Americas |
| 138 | ST_CLERK | 123.0 | 50 | 1500 | US | Stephen | Stiles | 3200.0 | NULL | Shipping | Stock Clerk | South San Francisco | California | United States of America | Americas |
| 139 | ST_CLERK | 123.0 | 50 | 1500 | US | John | Seo | 2700.0 | NULL | Shipping | Stock Clerk | South San Francisco | California | United States of America | Americas |
| 140 | ST_CLERK | 123.0 | 50 | 1500 | US | Joshua | Patel | 2500.0 | NULL | Shipping | Stock Clerk | South San Francisco | California | United States of America | Americas |
| 141 | ST_CLERK | 124.0 | 50 | 1500 | US | Trenna | Rajs | 3500.0 | NULL | Shipping | Stock Clerk | South San Francisco | California | United States of America | Americas |
| 142 | ST_CLERK | 124.0 | 50 | 1500 | US | Curtis | Davies | 3100.0 | NULL | Shipping | Stock Clerk | South San Francisco | California | United States of America | Americas |
| 143 | ST_CLERK | 124.0 | 50 | 1500 | US | Randall | Matos | 2600.0 | NULL | Shipping | Stock Clerk | South San Francisco | California | United States of America | Americas |
| 144 | ST_CLERK | 124.0 | 50 | 1500 | US | Peter | Vargas | 2500.0 | NULL | Shipping | Stock Clerk | South San Francisco | California | United States of America | Americas |
| 180 | SH_CLERK | 120.0 | 50 | 1500 | US | Winston | Taylor | 3200.0 | NULL | Shipping | Shipping Clerk | South San Francisco | California | United States of America | Americas |
| 181 | SH_CLERK | 120.0 | 50 | 1500 | US | Jean | Fleaur | 3100.0 | NULL | Shipping | Shipping Clerk | South San Francisco | California | United States of America | Americas |
| 182 | SH_CLERK | 120.0 | 50 | 1500 | US | Martha | Sullivan | 2500.0 | NULL | Shipping | Shipping Clerk | South San Francisco | California | United States of America | Americas |
| 183 | SH_CLERK | 120.0 | 50 | 1500 | US | Girard | Geoni | 2800.0 | NULL | Shipping | Shipping Clerk | South San Francisco | California | United States of America | Americas |
| 184 | SH_CLERK | 121.0 | 50 | 1500 | US | Nandita | Sarchand | 4200.0 | NULL | Shipping | Shipping Clerk | South San Francisco | California | United States of America | Americas |
| 185 | SH_CLERK | 121.0 | 50 | 1500 | US | Alexis | Bull | 4100.0 | NULL | Shipping | Shipping Clerk | South San Francisco | California | United States of America | Americas |
| 186 | SH_CLERK | 121.0 | 50 | 1500 | US | Julia | Dellinger | 3400.0 | NULL | Shipping | Shipping Clerk | South San Francisco | California | United States of America | Americas |
| 187 | SH_CLERK | 121.0 | 50 | 1500 | US | Anthony | Cabrio | 3000.0 | NULL | Shipping | Shipping Clerk | South San Francisco | California | United States of America | Americas |
| 188 | SH_CLERK | 122.0 | 50 | 1500 | US | Kelly | Chung | 3800.0 | NULL | Shipping | Shipping Clerk | South San Francisco | California | United States of America | Americas |
| 189 | SH_CLERK | 122.0 | 50 | 1500 | US | Jennifer | Dilly | 3600.0 | NULL | Shipping | Shipping Clerk | South San Francisco | California | United States of America | Americas |
| 190 | SH_CLERK | 122.0 | 50 | 1500 | US | Timothy | Gates | 2900.0 | NULL | Shipping | Shipping Clerk | South San Francisco | California | United States of America | Americas |
| 191 | SH_CLERK | 122.0 | 50 | 1500 | US | Randall | Perkins | 2500.0 | NULL | Shipping | Shipping Clerk | South San Francisco | California | United States of America | Americas |
| 192 | SH_CLERK | 123.0 | 50 | 1500 | US | Sarah | Bell | 4000.0 | NULL | Shipping | Shipping Clerk | South San Francisco | California | United States of America | Americas |
| 193 | SH_CLERK | 123.0 | 50 | 1500 | US | Britney | Everett | 3900.0 | NULL | Shipping | Shipping Clerk | South San Francisco | California | United States of America | Americas |
| 194 | SH_CLERK | 123.0 | 50 | 1500 | US | Samuel | McCain | 3200.0 | NULL | Shipping | Shipping Clerk | South San Francisco | California | United States of America | Americas |
| 195 | SH_CLERK | 123.0 | 50 | 1500 | US | Vance | Jones | 2800.0 | NULL | Shipping | Shipping Clerk | South San Francisco | California | United States of America | Americas |
| 196 | SH_CLERK | 124.0 | 50 | 1500 | US | Alana | Walsh | 3100.0 | NULL | Shipping | Shipping Clerk | South San Francisco | California | United States of America | Americas |
| 197 | SH_CLERK | 124.0 | 50 | 1500 | US | Kevin | Feeney | 3000.0 | NULL | Shipping | Shipping Clerk | South San Francisco | California | United States of America | Americas |
| 198 | SH_CLERK | 124.0 | 50 | 1500 | US | Donald | OConnell | 2600.0 | NULL | Shipping | Shipping Clerk | South San Francisco | California | United States of America | Americas |
| 199 | SH_CLERK | 124.0 | 50 | 1500 | US | Douglas | Grant | 2600.0 | NULL | Shipping | Shipping Clerk | South San Francisco | California | United States of America | Americas |
| 103 | IT_PROG | 102.0 | 60 | 1400 | US | Alexander | Hunold | 9000.0 | NULL | IT | Programmer | Southlake | Texas | United States of America | Americas |
| 104 | IT_PROG | 103.0 | 60 | 1400 | US | Bruce | Ernst | 6000.0 | NULL | IT | Programmer | Southlake | Texas | United States of America | Americas |
| 105 | IT_PROG | 103.0 | 60 | 1400 | US | David | Austin | 4800.0 | NULL | IT | Programmer | Southlake | Texas | United States of America | Americas |
| 106 | IT_PROG | 103.0 | 60 | 1400 | US | Valli | Pataballa | 4800.0 | NULL | IT | Programmer | Southlake | Texas | United States of America | Americas |
| 107 | IT_PROG | 103.0 | 60 | 1400 | US | Diana | Lorentz | 4200.0 | NULL | IT | Programmer | Southlake | Texas | United States of America | Americas |
| 204 | PR_REP | 101.0 | 70 | 2700 | DE | Hermann | Baer | 10000.0 | NULL | Public Relations | Public Relations Representative | Munich | Bavaria | Germany | Europe |
| 145 | SA_MAN | 100.0 | 80 | 2500 | UK | John | Russell | 14000.0 | 0.40 | Sales | Sales Manager | Oxford | Oxford | United Kingdom | Europe |
| 146 | SA_MAN | 100.0 | 80 | 2500 | UK | Karen | Partners | 13500.0 | 0.30 | Sales | Sales Manager | Oxford | Oxford | United Kingdom | Europe |
| 147 | SA_MAN | 100.0 | 80 | 2500 | UK | Alberto | Errazuriz | 12000.0 | 0.30 | Sales | Sales Manager | Oxford | Oxford | United Kingdom | Europe |
| 148 | SA_MAN | 100.0 | 80 | 2500 | UK | Gerald | Cambrault | 11000.0 | 0.30 | Sales | Sales Manager | Oxford | Oxford | United Kingdom | Europe |
| 149 | SA_MAN | 100.0 | 80 | 2500 | UK | Eleni | Zlotkey | 10500.0 | 0.20 | Sales | Sales Manager | Oxford | Oxford | United Kingdom | Europe |
| 150 | SA_REP | 145.0 | 80 | 2500 | UK | Peter | Tucker | 10000.0 | 0.30 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 151 | SA_REP | 145.0 | 80 | 2500 | UK | David | Bernstein | 9500.0 | 0.25 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 152 | SA_REP | 145.0 | 80 | 2500 | UK | Peter | Hall | 9000.0 | 0.25 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 153 | SA_REP | 145.0 | 80 | 2500 | UK | Christopher | Olsen | 8000.0 | 0.20 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 154 | SA_REP | 145.0 | 80 | 2500 | UK | Nanette | Cambrault | 7500.0 | 0.20 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 155 | SA_REP | 145.0 | 80 | 2500 | UK | Oliver | Tuvault | 7000.0 | 0.15 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 156 | SA_REP | 146.0 | 80 | 2500 | UK | Janette | King | 10000.0 | 0.35 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 157 | SA_REP | 146.0 | 80 | 2500 | UK | Patrick | Sully | 9500.0 | 0.35 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 158 | SA_REP | 146.0 | 80 | 2500 | UK | Allan | McEwen | 9000.0 | 0.35 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 159 | SA_REP | 146.0 | 80 | 2500 | UK | Lindsey | Smith | 8000.0 | 0.30 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 160 | SA_REP | 146.0 | 80 | 2500 | UK | Louise | Doran | 7500.0 | 0.30 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 161 | SA_REP | 146.0 | 80 | 2500 | UK | Sarath | Sewall | 7000.0 | 0.25 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 162 | SA_REP | 147.0 | 80 | 2500 | UK | Clara | Vishney | 10500.0 | 0.25 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 163 | SA_REP | 147.0 | 80 | 2500 | UK | Danielle | Greene | 9500.0 | 0.15 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 164 | SA_REP | 147.0 | 80 | 2500 | UK | Mattea | Marvins | 7200.0 | 0.10 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 165 | SA_REP | 147.0 | 80 | 2500 | UK | David | Lee | 6800.0 | 0.10 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 166 | SA_REP | 147.0 | 80 | 2500 | UK | Sundar | Ande | 6400.0 | 0.10 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 167 | SA_REP | 147.0 | 80 | 2500 | UK | Amit | Banda | 6200.0 | 0.10 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 168 | SA_REP | 148.0 | 80 | 2500 | UK | Lisa | Ozer | 11500.0 | 0.25 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 169 | SA_REP | 148.0 | 80 | 2500 | UK | Harrison | Bloom | 10000.0 | 0.20 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 170 | SA_REP | 148.0 | 80 | 2500 | UK | Tayler | Fox | 9600.0 | 0.20 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 171 | SA_REP | 148.0 | 80 | 2500 | UK | William | Smith | 7400.0 | 0.15 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 172 | SA_REP | 148.0 | 80 | 2500 | UK | Elizabeth | Bates | 7300.0 | 0.15 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 173 | SA_REP | 148.0 | 80 | 2500 | UK | Sundita | Kumar | 6100.0 | 0.10 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 174 | SA_REP | 149.0 | 80 | 2500 | UK | Ellen | Abel | 11000.0 | 0.30 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 175 | SA_REP | 149.0 | 80 | 2500 | UK | Alyssa | Hutton | 8800.0 | 0.25 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 176 | SA_REP | 149.0 | 80 | 2500 | UK | Jonathon | Taylor | 8600.0 | 0.20 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 177 | SA_REP | 149.0 | 80 | 2500 | UK | Jack | Livingston | 8400.0 | 0.20 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 179 | SA_REP | 149.0 | 80 | 2500 | UK | Charles | Johnson | 6200.0 | 0.10 | Sales | Sales Representative | Oxford | Oxford | United Kingdom | Europe |
| 100 | AD_PRES | NULL | 90 | 1700 | US | Steven | King | 24000.0 | NULL | Executive | President | Seattle | Washington | United States of America | Americas |
| 101 | AD_VP | 100.0 | 90 | 1700 | US | Neena | Kochhar | 17000.0 | NULL | Executive | Administration Vice President | Seattle | Washington | United States of America | Americas |
| 102 | AD_VP | 100.0 | 90 | 1700 | US | Lex | De Haan | 17000.0 | NULL | Executive | Administration Vice President | Seattle | Washington | United States of America | Americas |
| 108 | FI_MGR | 101.0 | 100 | 1700 | US | Nancy | Greenberg | 12008.0 | NULL | Finance | Finance Manager | Seattle | Washington | United States of America | Americas |
| 109 | FI_ACCOUNT | 108.0 | 100 | 1700 | US | Daniel | Faviet | 9000.0 | NULL | Finance | Accountant | Seattle | Washington | United States of America | Americas |
| 110 | FI_ACCOUNT | 108.0 | 100 | 1700 | US | John | Chen | 8200.0 | NULL | Finance | Accountant | Seattle | Washington | United States of America | Americas |
| 111 | FI_ACCOUNT | 108.0 | 100 | 1700 | US | Ismael | Sciarra | 7700.0 | NULL | Finance | Accountant | Seattle | Washington | United States of America | Americas |
| 112 | FI_ACCOUNT | 108.0 | 100 | 1700 | US | Jose Manuel | Urman | 7800.0 | NULL | Finance | Accountant | Seattle | Washington | United States of America | Americas |
| 113 | FI_ACCOUNT | 108.0 | 100 | 1700 | US | Luis | Popp | 6900.0 | NULL | Finance | Accountant | Seattle | Washington | United States of America | Americas |
| 205 | AC_MGR | 101.0 | 110 | 1700 | US | Shelley | Higgins | 12008.0 | NULL | Accounting | Accounting Manager | Seattle | Washington | United States of America | Americas |
| 206 | AC_ACCOUNT | 205.0 | 110 | 1700 | US | William | Gietz | 8300.0 | NULL | Accounting | Public Accountant | Seattle | Washington | United States of America | Americas |
| Total Rows: 106, Total Columns: 16 | |||||||||||||||
Reflection
- Please add a paragraph or two about how this project went.
- At first it was really daunting, all of the installing. I have like computer science PTSD when it comes to making sure everything is installed the right way! It has always been one of the most frusturating parts for no reason! Making sure everything is installed correctly just for it to work is the last thing I should be worrying about! However, it does show a mastery in systems to be able to move through one efficiently and understand why it works the way it does. Having a mastery in
- Was it harder or easier than you expected?
- It was about as difficult as I expected. Professor said this assigment was going to be slightly trickier, so I took my time. It was relatively difficult, but I have finished all components. Just took a lot of debugging and time.
- Did the instructor provide too much information or not enough information?
- The instructor provided the perfect information! However, at the end, I still had to install a few more modules, and there was one grammatical error in helpers.py. These things could throw someone off, and it might send them back to the tutorial instead of realizing these errors are quick fixes. Maybe pandas is installed on other systems automatically though.
- Do you have suggestions for how it can be improved?
- As I said right above. There is one grammatical error in helpers.py and there could be a clarification that the user might need to download separately a few modules. I know we had to avoid using pip earlier, so all of this could be confusing. Otherwise, perfect instruction! The .env could also be mentioned as needing to be edited.
README
Homework 5 - CMSC408 - Spring 2025
This will be the first homework to involve SQL! We are going to installing necessary python packages to get SQL up and running while Quarto converts our code to HTML through VSCode! This will set us up for SQL to end the semester and it will give us the tools to design our own database using SQL!
Homework tasks
Together, we will:
Install the necessary python tools and libraries
Verify that the tools and libraries are installed.
Verify that you can log into the phpMyAdmin site.
Verify that your VSCODE can connect to the mySql server.
Explore queries and result sets using pandas
Explore Quarto as a tool for writing out tables.
Explore and document a new database that some provided to you.
Render explore.qmd to ensure that everything works.
Complete the report.qmd scaffold and render the report!
Files and folders
All necessary files are stored in the ./reports folder.